package com.vsked.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.vsked.bean.Column;
import com.vsked.bean.ConnectionInfo;
import com.vsked.bean.VirtualBean;
import com.vsked.connection.ConnectionUtil;
import com.vsked.util.BeanProcess;
import com.vsked.util.TransFormDataType;

public class ColumnInfo {
	private static Connection sqlServerConn = null;
	private Connection mySqlConn = null;
	private Connection oracleConn = null;
	private static String sql = "";
    
	private static ConnectionUtil cu=new ConnectionUtil();
	private static int tableColumnCount = 0;

	public static List<String> getTableColumnNames(ConnectionInfo inCI, String inTableName) {
		List<String> tableColumnName = null;
		try {
			sqlServerConn = cu.getSqlServer2000Connection(inCI);
			sql = "select name from syscolumns where id=(select id from sysobjects where xtype='u' and name='"
					+ inTableName + "')";
			ResultSet rs = sqlServerConn.createStatement().executeQuery(sql);
			tableColumnName =new ArrayList<String>();
			while (rs.next()) {
				if (tableColumnName == null) {
					tableColumnName =new ArrayList<String>();
				}
				tableColumnName.add(rs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return tableColumnName;
	}
	
	public static List<Map> getTableColumnNameAndDataType(ConnectionInfo inCI, String inTableName) {
		List<Map> tableColumnName = new ArrayList<Map>();
		try {
			if("MsSqlServer2000".equals(inCI.getDbType())){
				sqlServerConn = cu.getSqlServer2000Connection(inCI);
				sql = "select * from syscolumns where id=(select id from sysobjects where xtype='u' and name='"	+ inTableName + "')";
				ResultSet rs = sqlServerConn.createStatement().executeQuery(sql);
				while (rs.next()) {
					Map tempInfo=new HashMap();
					tempInfo.put(rs.getString("name"), rs.getInt("xtype"));
					BeanProcess.forDAOInfoMethodString+=BeanProcess.getObjectFiledSet(inTableName,rs.getString("name"),TransFormDataType.getSqlServerDataType(rs.getInt("xtype")));
					tableColumnName.add(tempInfo);
				}
			}
			if("MySql".equals(inCI.getDbType())){
				sqlServerConn=cu.getMySqlConnection(inCI);
				sql="desc "+inTableName;
				ResultSet rs=sqlServerConn.createStatement().executeQuery(sql);
				while(rs.next()){
					Map tempInfo=new HashMap();
					String fieldStr=rs.getString("field");
					String typeStr=(rs.getString("type").indexOf("(")>1
							?
									rs.getString("type").substring(0,rs.getString("type").indexOf("(")):rs.getString("type")
									);
					tempInfo.put(fieldStr, typeStr);
					BeanProcess.forDAOInfoMethodString+=BeanProcess.getObjectFiledSet(inTableName,rs.getString("field"),TransFormDataType.getMySqlDataType(typeStr));
					tableColumnName.add(tempInfo);
				}
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return tableColumnName;
	}
	
	public static String getTableColumnDataTypeByColumnName(ConnectionInfo inCI, String inTableName,String inColumnName) {
		int columnDataType=167;
		List<Map> tableColumnName = null;
		try {
			sqlServerConn = cu.getSqlServer2000Connection(inCI);
			sql = "select * from syscolumns where id=(select id from sysobjects where xtype='u' and name='"	+ inTableName + "') and [name]='"+inColumnName+"'";
			ResultSet rs = sqlServerConn.createStatement().executeQuery(sql);
			tableColumnName =new ArrayList<Map>();
			while (rs.next()) {
				columnDataType=rs.getInt("xtype");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return TransFormDataType.getSqlServerDataType(columnDataType);
	}
	
	public static List<VirtualBean> getTableColumnNameAndDataTypeAndValue(ConnectionInfo inCI, String inTableName) {
		List<VirtualBean> tableColumnName = null;
		try {
			sqlServerConn = cu.getSqlServer2000Connection(inCI);
			sql = "select * from syscolumns where id=(select id from sysobjects where xtype='u' and name='"	+ inTableName + "')";
			ResultSet rs = sqlServerConn.createStatement().executeQuery(sql);
			tableColumnName =new ArrayList<VirtualBean>();
			while (rs.next()) {
				if (tableColumnName == null) {
					tableColumnName =new ArrayList<VirtualBean>();
				}
				//TODO
				//tempInfo.put(rs.getString("name"), rs.getInt("xtype"));
				//tableColumnName.add(new VirtualBean(rs.getString("name"),));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return tableColumnName;
	}
	
	public static Map getTableColumnNameAndDataTypeSet(ConnectionInfo inCI, String inTableName) {
		Map vb=new HashMap();
		try {
			sqlServerConn = cu.getSqlServer2000Connection(inCI);
            TableInfo tableInfo=new TableInfo();
            ColumnInfo columnInfo=new ColumnInfo();
			List<String> tableName = tableInfo.getSqlServerTableName(inCI);
            System.out.println(":"+tableName.size());
			for (int tableIndex = 0; tableIndex < tableName.size(); tableIndex++) {
				System.out.println(tableIndex + "_||_" + tableName.get(tableIndex));
				List<Map> columnName=columnInfo.getTableColumnNameAndDataType(inCI, tableName.get(tableIndex));
				System.out.println(":"+columnName.size());
				for(int columnIndex=0;columnIndex<columnName.size();columnIndex++){
					Map columnMap=columnName.get(columnIndex);
					Iterator it=columnMap.entrySet().iterator();
					List<VirtualBean> columnList=new ArrayList<VirtualBean>();
					while(it.hasNext()){
						Map.Entry me=(Map.Entry)it.next();
						System.out.println(":"+me.getKey()+":"+me.getValue());
						columnList.add(new VirtualBean(inCI.getDbName()+"."+tableName.get(tableIndex)+"."+me.getKey(),"",me.getValue()));						
					}
				}//end columnIndex				

			}// end for tableIndex
		} catch (Exception e) {
			e.printStackTrace();
		}// end catch
		//TODO will do 
		return null;
	}

	public static int getTableColumnCount(ConnectionInfo inCI, String inTableName) {
		try {
			sqlServerConn = cu.getSqlServer2000Connection(inCI);
			sql = "select count(*) from syscolumns where id=(select id from sysobjects where xtype='u' and name='"
					+ inTableName + "')";
			PreparedStatement pt = sqlServerConn.prepareStatement(sql);
			ResultSet rs = pt.executeQuery();
			if (rs.next()) {
				tableColumnCount = rs.getInt(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return tableColumnCount;
	}
	public static List<Column> getColumnsInfoByTableName(ConnectionInfo inCI, String inTableName){
		List<Column> columnList=new ArrayList<Column>();
		try {
			sqlServerConn = cu.getSqlServer2000Connection(inCI);
			sql = "select sc.name,st.name,sc.isnullable,sc.length from syscolumns sc,systypes st where sc.xusertype=st.xusertype and sc.id=object_id(?)";
			PreparedStatement pt = sqlServerConn.prepareStatement(sql);
			pt.setString(1, inTableName);
			ResultSet rs = pt.executeQuery();
			while(rs.next()) {
				columnList.add(new Column(rs.getString(1),rs.getString(2),rs.getBoolean(3),rs.getInt(4)));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return columnList;
		
	}

}// end
